USE [paws]
GO

/****** Object:  UserDefinedFunction [dbo].[CO2Emissions_Lookup]    Script Date: 05/30/2012 15:14:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[CO2Emissions_Lookup] (
	@RefDate datetime
) RETURNS smallint

BEGIN
	
	declare @co2PerUnit smallint
	
	set @co2PerUnit = (SELECT CO2PerUnit from CO2Emissions where @RefDate >= FromDate and (@RefDate <= ToDate or ToDate is null))

	RETURN @co2PerUnit
	
END


GO


USE [paws]
GO

/****** Object:  UserDefinedFunction [dbo].[ComputerConsumptions_Lookup]    Script Date: 05/30/2012 15:14:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ComputerConsumptions_Lookup] (
	@ClientName varchar(255),
	@RefDate datetime
) RETURNS smallint

BEGIN
	
	DECLARE @recordCount int
	
	SET @recordCount = (SELECT count(*) from ComputerConsumptions where ClientName = @ClientName ); 
	
	if @recordCount = 0
		SET @ClientName = 'DEFAULT_CONSUMPTION'
	
	declare @total smallint 
	
	set @total = (select BaseConsumption from ComputerConsumptions where ClientName = @ClientName)
					+(select MonitorConsumption from ComputerConsumptions where ClientName = @ClientName)

	RETURN @total
END


GO


USE [paws]
GO

/****** Object:  UserDefinedFunction [dbo].[ElectricityPrices_Lookup]    Script Date: 05/30/2012 15:15:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ElectricityPrices_Lookup] (
	@RefDate datetime
) RETURNS decimal(10,10)

BEGIN
	
	DECLARE @elecPrice decimal(10,10)
	
	set @elecPrice = (SELECT UnitPrice from ElectricityPrices where @RefDate >= FromDate and (@RefDate <= ToDate or ToDate is null))

	RETURN @elecPrice
END


GO

USE [paws]
GO

/****** Object:  UserDefinedFunction [dbo].[SavingStatisitics_Validate]    Script Date: 05/30/2012 15:15:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[SavingStatisitics_Validate] (
	@OU varchar(750),
	@RefDate datetime
) RETURNS smallint

BEGIN
	
	Declare @Total bigint
	
	set @Total = (select count(*) from SavingStatistics where OU = @OU and Date = @RefDate)
	
	if @Total > 0
		return 0
	
	return 1
	
END


GO


USE [paws]
GO

/****** Object:  UserDefinedFunction [dbo].[Generic_GetUtilisation]    Script Date: 05/30/2012 15:15:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[Generic_GetUtilisation] (
	@FromTime datetime,
	@ToTime datetime
) RETURNS @sList TABLE (date datetime ,period1 bigint, period2 bigint, period3 bigint)

BEGIN
	
	declare @period bigint
	declare @marker datetime
	declare @PeriodMarker datetime 
	declare @UTIL_0000_0800 bigint, @UTIL_0800_1800 bigint, @UTIL_1800_0000 bigint, @CurrentDate datetime
	set @UTIL_0000_0800 = 0
	set @UTIL_0800_1800 = 0
	set @UTIL_1800_0000 = 0
	
	declare @dateChange smallint
	set @dateChange = 0
	
	while (@FromTime < @ToTime)
	begin
			
		declare @fromHour int
		set @fromHour = datepart(hh, @FromTime)
		
		if @fromHour >= 0 and @fromHour < 8 
			set @PeriodMarker =   DATEADD(hh,8,DATEADD(dd, 0, DATEDIFF(dd, 0, @FromTime)))
		else if @fromHour >= 8 and @fromHour < 18 
			set @PeriodMarker =   DATEADD(hh,18,DATEADD(dd, 0, DATEDIFF(dd, 0, @FromTime)))
		else if @fromHour >= 18 and @fromHour < 24
		begin
			set @PeriodMarker =   DATEADD(hh,24,DATEADD(dd, 0, DATEDIFF(dd, 0, @FromTime)))
			set @dateChange = 1
		end
		
		-- if login period overlaps time period
		if @ToTime > @PeriodMarker
		begin
			if @fromHour >= 0 and @fromHour < 8 
				set @UTIL_0000_0800 = datediff(ms,@FromTime,@PeriodMarker)
			else if @fromHour >= 8 and @fromHour < 18 
				set @UTIL_0800_1800 = datediff(ms,@FromTime,@PeriodMarker)
			else if @fromHour >= 18 and @fromHour < 24
				set @UTIL_1800_0000 = datediff(ms,@FromTime,@PeriodMarker)
	
			set @CurrentDate = @FromTime
			set @FromTime = @PeriodMarker
		end
		
		else
		begin
			if @fromHour >= 0 and @fromHour < 8 
				set @UTIL_0000_0800 = datediff(ms,@FromTime,@ToTime)
			else if @fromHour >= 8 and @fromHour < 18 
				set @UTIL_0800_1800 = datediff(ms,@FromTime,@ToTime)
			else if @fromHour >= 18 and @fromHour < 24
				set @UTIL_1800_0000 = datediff(ms,@FromTime,@ToTime)
			
			set @CurrentDate = @FromTime
			set @FromTime = @ToTime
			set @dateChange = 1
		end
		
		if @dateChange = 1 
		begin
		
			set @CurrentDate =   DATEADD(ms,0,DATEADD(ms, 0, DATEDIFF(dd, 0, @CurrentDate)))
			insert into @sList values (@CurrentDate,@UTIL_0000_0800,@UTIL_0800_1800,@UTIL_1800_0000)
			set @UTIL_0000_0800 = 0
			set @UTIL_0800_1800 = 0
			set @UTIL_1800_0000 = 0
			set @dateChange = 0
		end	
	end

	RETURN
END

GO


USE [paws]
GO

/****** Object:  UserDefinedFunction [dbo].[paws_split_comma_separated_field]    Script Date: 05/30/2012 15:16:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[paws_split_comma_separated_field] (
	@computerlist varchar(max),
	@sDelimiter varchar(10)
) RETURNS @sList TABLE (id int IDENTITY, computer VARCHAR(255))

BEGIN
	DECLARE @sItem VARCHAR(max)
	
	WHILE CHARINDEX(@sDelimiter,@computerlist,0) <> 0
	BEGIN
SELECT
	@sItem=RTRIM(LTRIM(SUBSTRING(@computerlist,1,CHARINDEX(@sDelimiter,@computerlist,0)-1))),
	@computerlist=RTRIM(LTRIM(SUBSTRING(@computerlist,CHARINDEX(@sDelimiter,@computerlist,0)+LEN(@sDelimiter),LEN(@computerlist))))

	IF LEN(@sItem) > 0
		INSERT INTO @sList SELECT @sItem
	END
	
	IF LEN(@computerlist) > 0
		INSERT INTO @sList SELECT @computerList -- Put the last item in
		RETURN
END

GO



